package rpt.yto.flex.remote.data.dao.processor;

import java.util.Map;

public class Query_joinpsn_corp {
	
	public Map<String , String> distinctionMap = null;
	public Map<String , String> deptMap = null;

	private static Query_joinpsn_corp querySqlFunc = new Query_joinpsn_corp();
	
	private Query_joinpsn_corp() {
		
	}
	
	public static final Query_joinpsn_corp getInstance() {
		return querySqlFunc;
	}
	
	public final String getQuerySQL(String period) throws Exception {
		
		StringBuilder builderSql = new StringBuilder();
		
		builderSql.append(" select rownum rowno , temp.* from ( ");
		builderSql.append(" select statistictype  , sum(endcount) endcount , sum(endcount_w) endcount_w , sum(comcount) comcount , sum(mngcount) mngcount , ");
		builderSql.append(" sum(expcount) expcount , sum(expcount_acc) expcount_acc , sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount ");
		builderSql.append(" from ( select distinct psn.psncode , psn.unitcode || ' - ' || psn.unitshortname  statistictype  ,  1 endcount , ");
		builderSql.append(" case when psn.sex = '女' then 1 else 0 end endcount_w  , ");
		builderSql.append(" case when psn.jobname like '%主管' or psn.jobname like '%经理' or psn.jobname like '%总监' or psn.jobname like '%总裁' or psn.jobname like '%高级专员'  or  psn.jobname like '%专家' then 1 else 0 end comcount , ");
		builderSql.append(" case when psn.jobname like '%经理' or psn.jobname like '%总监' or psn.jobname like '%总裁' or psn.jobname like '%高级专员'  or  psn.jobname like '%专家' then 1 else 0 end mngcount , ");
		builderSql.append(" case when psn.jobname like '%业务员' then 1 else 0 end expcount , ");
		builderSql.append(" case when psn.jobname like '%业务员' and nvl(psn.groupdef1 , 'N') = 'Y' then 1 else 0 end expcount_acc , ");
		builderSql.append(" case when psn.jobname like '操作%' then 1 else 0 end roundcount , ");
		builderSql.append(" case when psn.jobname like '操作%' and nvl(psn.groupdef1 , 'N') = 'Y' then 1 else 0 end roundcount_acc , ");
		builderSql.append(" case when psn.jobname like '%客%' then 1 else 0 end cscount ");
		builderSql.append(" from vw_yto_psn_2 psn , vw_yto_base_org_dept dept , vw_yto_corp_level_2 corp , bd_defdoc defdoc , om_job job ");
		builderSql.append(" where psn.pk_deptdoc = dept.pk_deptdoc and psn.unitdistinction = defdoc.pk_defdoc(+) ");
		builderSql.append(" and psn.pk_om_job = job.pk_om_job(+) ");
		builderSql.append(" and psn.pk_corp = corp.pk_corp(+) and ((psn.psnclscope = '0' and psn.outdutydate is null) or psn.outdutydate > '"+period+"~') ");
		builderSql.append(" and	(psn.isseal = 'N' or psn.sealeddate >= '"+period+"~' )	 ");
		builderSql.append(" and	(psn.isworkingunit = 'N') ");
		builderSql.append(" and (nvl(job.isabort , 'N') = 'N' or job.abortdate like '"+period+"%') ");
//		builderSql.append(" and psn.unitdistinction = 'ORGCORP00000000A0019' ");
		builderSql.append(" and psn.indutydate <= '"+period+"~' ");
		builderSql.append(" ) group by statistictype order by statistictype ) temp ");
		
		return builderSql.toString();
	}
	
//	/**
//	 * 
//	 * 全网按省（区）聚合表 - distinctionMap中的类别
//	 * 
//	 * @param period
//	 * @return
//	 * @throws Exception
//	 */
//	public final String getQueryTypeByMap(String period , String pk_corp , Integer rowno , String statistictype) throws Exception {
//			
//			StringBuilder builderTypeByMap = new StringBuilder();
//			builderTypeByMap.append(" select "+rowno+" rowno , '"+statistictype+"' statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
//			builderTypeByMap.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
//			builderTypeByMap.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
//			builderTypeByMap.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");
//			// 期末从业人员数
//			builderTypeByMap.append(" select count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount "); 
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 期末女性从业人员数
//			builderTypeByMap.append(" select 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and bd_psnbasdoc.sex = '女' ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 主管以上管理人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁') ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 经理级以上人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁') ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 专业技术人员数
//			// procount
//			// 取得技术职称人员数
//			// procount_acc
//			// 技能人员数 
//			// skillcount 
//			// 取得技能资格证人员数
//			// skillcount_acc
//			// 快递业务人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '%业务员') ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 取得业务员资格证人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 分拣操作人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '操作%') ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 取得操作员资格证人员
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" union all  ");
//			// 客服人员数
//			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
//			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
//			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
//			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
//			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
//			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
//			builderTypeByMap.append("  and bd_psndoc.psnclscope = 0 ");
//			builderTypeByMap.append("  and bd_psndoc.outdutydate is null and bd_psndoc.indutydate <= '"+period+"' || '~' ");
//			builderTypeByMap.append(" and nvl(om_job.isabort , 'N') = 'N' and bd_corp.isseal = 'N' ");
//			builderTypeByMap.append(" and nvl(bd_corp.isworkingunit , 'N') = 'N'  ");
//			builderTypeByMap.append(" and bd_corp.unitdistinction = 'ORGCORP00000000A0019' ");
//			builderTypeByMap.append(getCorpJoinString(pk_corp));
//			
//			builderTypeByMap.append(" and (om_job.jobname like '%客%') ) ");
//			
//			
//			return builderTypeByMap.toString();
//		
//		
//	}
//	
//	
//	public final Map<String , String> initDistinctionMap() throws Exception {
//		
//		distinctionMap = new ConcurrentHashMap<String , String>();
//		deptMap = new ConcurrentHashMap<String , String>();
//		
//		List<Map<String , Object>> corpList = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(" select pk_corp , unitname from bd_corp where bd_corp.isseal = 'N' and bd_corp.unitdistinction = 'ORGCORP00000000A0019' and nvl(bd_corp.isworkingunit , 'N') = 'N'  order by bd_corp.unitcode ", CorpBean.class);
//		
//		int rowno = 1;
//		for(Map<String , Object> corpVO : corpList) {
//			distinctionMap.put(String.valueOf(corpVO.get("pk_corp")), rowno + "~" + corpVO.get("unitname"));
//			rowno ++;
//		}
//		
//		return distinctionMap;
//		
//	}
//	
//	public final String getCorpJoinString(String pk_corp) throws Exception {
//		
//		if(deptMap.get(pk_corp) == null) {
//			List<Map<String , Object>> deptList = (List<Map<String, Object>>) QueryBiz.getInstance().executeQuery("select deptcode from bd_deptdoc where length(deptcode) = 6 and pk_corp = '"+pk_corp+"'", DeptdocBean.class);
//	
//			StringBuilder builderDeptcode = new StringBuilder();
//			if(deptList != null && deptList.size() > 0) {
//				for(Map<String , Object> dept : deptList) {
//					
//					if(dept.get("deptcode") == null || "".equals(dept.get("deptcode")))
//						continue;
//					
//					String queryString = "select to_char(wmsys.wm_concat(chr(39) || pk_deptdoc || chr(39))) || ',' pk_deptdoc from bd_deptdoc where deptcode like '"+dept.get("deptcode")+"%' and pk_corp = '"+pk_corp+"'";
//					List<Map<String , Object>> pk_deptdoc = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(queryString, DeptdocBean.class);
//					
//					if(",".equals(pk_deptdoc.get(0).get("pk_deptdoc")))
//						continue ;
//					
//					builderDeptcode.append(pk_deptdoc.get(0).get("pk_deptdoc"));
//					
//				}
//			}
//			
//			String result = "";
//			if(builderDeptcode.length() > 0)
//				result = builderDeptcode.substring(0 , builderDeptcode.length() - 1);
//			
//			deptMap.put(pk_corp, result);
//		}
//		
//		if(deptMap.get(pk_corp) == null || "".equals(deptMap.get(pk_corp)))
//			return " and bd_corp.pk_corp = '"+pk_corp+"' ";
//		
//		return " and bd_corp.pk_corp = '"+pk_corp+"' and bd_psndoc.pk_deptdoc not in ("+deptMap.get(pk_corp)+") ";
//		
//	}
	
}















